SQL Server 2014 with PowerShell v5 Cookbook by Santos Donabel

Author:Santos, Donabel [Santos, Donabel]
Language: eng
Format: mobi
Publisher: Packt Publishing
Published: 2015-12-03T16:00:00+00:00

#need to establish unique ID for base table

$fullTextIndex.UniqueIndexName = "PK_Documents_DocumentID"

$fullTextIndex.Create()

The equivalent T-SQL statement to create this index is as follows:

CREATE FULLTEXT INDEX ON [dbo].[Documents]

KEY INDEX [PK_Documents_DocumentID]ON ([FTC])

WITH (STOPLIST = [SYSTEM])

With a full-text index, you can add additional text columns that can be searched. In our recipe, we add the Name columns to our index. This will allow the content of Name to be full-text searched:

#specify column for index

$documentNameCol = New-Object -TypeName Microsoft.SqlServer.Management.Smo.FullTextIndexColumn -ArgumentList $fullTextIndex, "Name"

$documentNameCol.Language = "English"

$documentNameCol.Create()

If you were to do this in T-SQL, this would be your script:

ALTER FULLTEXT INDEX

ON [dbo].[Documents]

ADD

(

[Name] LANGUAGE [English]

)

Because our table also has another field that specifies the document type, we can add this to our full-text index. We can specify this using the TypeColumnName property:

$documentContentCol = New-Object -TypeName Microsoft.SqlServer.Management.Smo.FullTextIndexColumn -ArgumentList $fullTextIndex, "Content"

$documentContentCol.Language = "English"

$documentContentCol.TypeColumnName = "Extension"

$documentContentCol.Create()

The addition of the type column to T-SQL is as follows:

ALTER FULLTEXT INDEX

ON [dbo].[Documents]

ADD

(

[Content] TYPE COLUMN [Extension] LANGUAGE [English]

)

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.